package com.gjs.bedorder.tools;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class JDBCTools {
	private static DataSource dataSource = null;
	
	static {
		initDataSource();
	}
	
	public static void initDataSource() {
//		attr.put("dataSource", getDataSource());
		dataSource = getDataSource();
	}
	
	public static BasicDataSource getDataSource() {
		BasicDataSource dataSource = null;
		Properties properties = new Properties();
		InputStream inStream = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
		try {
			properties.load(inStream);
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataSource;
	}
	
	public static Connection getConnection() {
		Connection con = null;
		try {
			con = dataSource.getConnection();
			return con;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void releaseDB(ResultSet result, Statement state, Connection con) {
		try {
			if (result != null && !result.isClosed())
				result.close();
			if (state != null && !state.isClosed())
				state.close();
//			if (con != null && con.isClosed())
//				con.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public static void releaseDB(ResultSet result) {
		if (result != null) {
			try {
				JDBCTools.releaseDB(result, result.getStatement(), 
						result.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	}
	
	public static int update(String sql, Object... args) {
		Connection con = getConnection();
		try {
			PreparedStatement preState = con.prepareStatement(sql);
			try {
				for (int i = 0; i < args.length; i++) {
					preState.setObject(i + 1, args[i]);
				}
				return preState.executeUpdate();
			} finally {
				releaseDB(null, preState, con);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public static int insert(String sql, Object... args) {
		return update(sql, args);
	}
	
	public static ResultSet query(String sql, Object... args) {
		Connection con = getConnection();
		try {
			PreparedStatement preState = con.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				preState.setObject(i + 1, args[i]);
			}
			return preState.executeQuery();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	
	public static <T> T getValue(String sql, Object... args) {
		ResultSet result = query(sql, args);
		try {
			try {
				if (result.next()) {
					return (T) result.getObject(1);
				}
			} finally {
				releaseDB(result);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static int queryHasData(String sql, Object... args) {
		ResultSet result = null;
		int count = 0;
		try {
			try {
				result = JDBCTools.query(sql, args);
				while (result.next()) {
					count++;
				}
			} finally {
				if (result != null)
					JDBCTools.releaseDB(result, result.getStatement(), 
							result.getStatement().getConnection());
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public static List<Map<String, Object>> getObjects(String sql, Object... args) {
		ResultSet result = query(sql, args);
		List<Map<String, Object>> objs = new ArrayList<Map<String,Object>>(14);
		try {
			try {
				while (result.next()) {
					objs.add(getObject(result));
				}
			} finally {
				JDBCTools.releaseDB(result, result.getStatement(), 
						result.getStatement().getConnection());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return objs;
	}
	
	public static <T> List<T> getObjects(Class<T> clazz, String sql, Object... args) {
		ResultSet result = query(sql, args);
		List<T> objs = new ArrayList<T>();
		try {
			try {
				while (result.next()) {
					objs.add(getObject(clazz, result));
				}
			} finally {
				JDBCTools.releaseDB(result, result.getStatement(), 
						result.getStatement().getConnection());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return objs;
	}
	
	public static <T> T getObject(Class<T> clazz, String sql, Object... args) {
		List<T> objs = getObjects(clazz, sql, args);
		T obj = null;
		if (!objs.isEmpty()) {
			obj = objs.get(0);
		}
		return obj;
	}
	
	public static Map<String, Object> getObject(ResultSet result) {
		try {
			
			List<String> labels = getColumnLabels(result.getMetaData());
			Map<String, Object> data = new HashMap<String, Object>();
			for (String columnName : labels) {
				Object obj = result.getObject(columnName);
				data.put(columnName, obj);
			}
			if (data.isEmpty()) {
				data = null;
			}
			
			return data;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static <T> T getObject(Class<T> clazz, ResultSet result) {
		try {
			//ResultSetMetaData rsmd = result.getMetaData();
			List<String> labels = getColumnLabels(result.getMetaData());
//			long curr = System.currentTimeMillis();
			labels = caseSensitiveTransform(clazz.getDeclaredFields(), labels);
//			System.out.println(System.currentTimeMillis() - curr);
			Map<String, Object> data = new HashMap<>();
			for (String columnName : labels) {
				Object value = result.getObject(columnName);
				data.put(columnName, value);
			}
			if (data.isEmpty()) {
				return null;
			}
			T obj = clazz.newInstance();
			for (Entry<String, Object> entry : data.entrySet()) {
				//ReflectionUtils.setFieldValue(obj, entry.getKey(), entry.getValue());
				BeanUtils.setProperty(obj, entry.getKey(), entry.getValue());
			}
			return obj;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private static List<String> getColumnLabels(ResultSetMetaData rsmd) {
		List<String> labels = new ArrayList<String>();
		try {
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				labels.add(rsmd.getColumnLabel(i + 1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return labels;
	}
	
	public static List<String> caseSensitiveTransform(Field[] fields, List<String> columnNames) {
		List<String> labels = new ArrayList<String>();
		int index = 0;
		int count = 0;
		for (String label : columnNames) {
			while (!label.equalsIgnoreCase(fields[index].getName())) {
				index++;
				if (index >= fields.length) {
					index = 0;
					count++;
					if (count > 4) {
						throw new RuntimeException("循环次数过多");
					}
				}
			}
			labels.add(fields[index++].getName());
			if (index == fields.length)
				index = 0;
		}
		return labels;
	}
	
}
